﻿using Animal.Error;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace BISTools.DBTools
{
    /// <summary>
    /// SQLHelper 常规版 页面增删改查用此版本
    /// </summary>
    public class SQLHelper : IDisposable
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        /// 

        private static readonly string Connection = ConfigurationManager.ConnectionStrings["BIS"].ConnectionString;

        public static string SysConnStr
        {
            get
            {
                return SQLHelper.Connection;
            }
        }
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
        {
            return SQLHelper.ExecuteNonQuery(null, cmdText, commandParameters);
        }
        public static int ExecuteNonQuery(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
        {
            return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, commandParameters);
        }
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection sqlConnection = null;
            int result;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                if (trans != null)
                {
                    sqlConnection = trans.Connection;
                }
                else
                {
                    sqlConnection = new SqlConnection(SQLHelper.SysConnStr);
                }
                SQLHelper.a(sqlCommand, sqlConnection, trans, cmdType, cmdText, commandParameters);
                int num = sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                result = num;
            }
            catch
            {
                throw;
            }
            finally
            {
                SQLHelper.a(sqlConnection, trans);
            }
            return result;
        }
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            return SQLHelper.ExecuteNonQuery(null, cmdType, cmdText, commandParameters);
        }
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText)
        {
            return SQLHelper.ExecuteNonQuery(trans, cmdType, cmdText, null);
        }
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            return SQLHelper.ExecuteNonQuery(null, cmdType, cmdText, null);
        }
        public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection sqlConnection = null;
            SqlDataReader result;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                if (trans != null)
                {
                    sqlConnection = trans.Connection;
                }
                else
                {
                    sqlConnection = new SqlConnection(SQLHelper.SysConnStr);
                }
                SQLHelper.a(sqlCommand, sqlConnection, trans, cmdType, cmdText, commandParameters);
                SqlDataReader sqlDataReader;
                if (trans != null)
                {
                    sqlDataReader = sqlCommand.ExecuteReader();
                }
                else
                {
                    sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                sqlCommand.Parameters.Clear();
                result = sqlDataReader;
            }
            catch
            {
                throw;
            }
            finally
            {
                SQLHelper.a(sqlConnection, trans);
            }
            return result;
        }
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            return SQLHelper.ExecuteReader(null, cmdType, cmdText, commandParameters);
        }
        public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText)
        {
            return SQLHelper.ExecuteReader(trans, cmdType, cmdText, null);
        }
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText)
        {
            return SQLHelper.ExecuteReader(null, cmdType, cmdText, null);
        }
        public static DataSet GetDataSet(string cmdText, params SqlParameter[] commandParameters)
        {
            return SQLHelper.GetDataSet(null, cmdText, commandParameters);
        }
        public static DataSet GetDataSet(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection sqlConnection = null;
            DataSet result;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                if (trans != null)
                {
                    sqlConnection = trans.Connection;
                }
                else
                {
                    sqlConnection = new SqlConnection(SQLHelper.SysConnStr);
                }
                SQLHelper.a(sqlCommand, sqlConnection, trans, CommandType.Text, cmdText, commandParameters);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet);
                sqlCommand.Parameters.Clear();
                sqlDataAdapter.Dispose();
                result = dataSet;
            }
            catch
            {
                throw;
            }
            finally
            {
                SQLHelper.a(sqlConnection, trans);
            }
            return result;
        }
        public static DataSet GetDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection sqlConnection = null;
            DataSet result;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                if (trans != null)
                {
                    sqlConnection = trans.Connection;
                }
                else
                {
                    sqlConnection = new SqlConnection(SQLHelper.SysConnStr);
                }
                SQLHelper.a(sqlCommand, sqlConnection, trans, cmdType, cmdText, commandParameters);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet);
                sqlCommand.Parameters.Clear();
                sqlDataAdapter.Dispose();
                result = dataSet;
            }
            catch
            {
                throw;
            }
            finally
            {
                SQLHelper.a(sqlConnection, trans);
            }
            return result;
        }
        public static DataSet GetDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            return SQLHelper.GetDataSet(null, cmdType, cmdText, commandParameters);
        }
        public static DataSet GetDataSet(SqlTransaction trans, CommandType cmdType, string cmdText)
        {
            return SQLHelper.GetDataSet(trans, cmdType, cmdText, null);
        }
        public static DataSet GetDataSet(CommandType cmdType, string cmdText)
        {
            return SQLHelper.GetDataSet(null, cmdType, cmdText, null);
        }
        public static DataSet GetDataSet_Conn(string strSysConnstr, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection sqlConnection = null;
            DataSet result;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlConnection = new SqlConnection(strSysConnstr);
                SQLHelper.a(sqlCommand, sqlConnection, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet);
                sqlCommand.Parameters.Clear();
                sqlDataAdapter.Dispose();
                result = dataSet;
            }
            catch
            {
                throw;
            }
            finally
            {
                SQLHelper.a(sqlConnection, null);
            }
            return result;
        }
        public static DataSet GetDataSet_Conn(string strSysConnstr, CommandType cmdType, string cmdText)
        {
            return SQLHelper.GetDataSet_Conn(strSysConnstr, cmdType, cmdText, null);
        }
        public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
        {
            return ExecuteScalar(null, CommandType.Text, cmdText, commandParameters);
        }
        public static object ExecuteScalar(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
        {
            return ExecuteScalar(trans, CommandType.Text, cmdText, commandParameters);
        }
        public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection sqlConnection = null;
            object result;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                if (trans != null)
                {
                    sqlConnection = trans.Connection;
                }
                else
                {
                    sqlConnection = new SqlConnection(SQLHelper.SysConnStr);
                }
                SQLHelper.a(sqlCommand, sqlConnection, trans, cmdType, cmdText, commandParameters);
                object obj = sqlCommand.ExecuteScalar();
                sqlCommand.Parameters.Clear();
                result = obj;
            }
            catch
            {
                throw;
            }
            finally
            {
                SQLHelper.a(sqlConnection, trans);
            }
            return result;
        }
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            return SQLHelper.ExecuteScalar(null, cmdType, cmdText, commandParameters);
        }
        public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText)
        {
            return SQLHelper.ExecuteScalar(trans, cmdType, cmdText, null);
        }
        public static object ExecuteScalar(CommandType cmdType, string cmdText)
        {
            return SQLHelper.ExecuteScalar(null, cmdType, cmdText, null);
        }
        private static void a(SqlCommand A_0, SqlConnection A_1, SqlTransaction A_2, CommandType A_3, string A_4, SqlParameter[] A_5)
        {
            try
            {
                if (A_1.State != ConnectionState.Open)
                {
                    A_1.Open();
                }
                A_0.Connection = A_1;
                A_0.CommandText = A_4;
                if (A_2 != null)
                {
                    A_0.Transaction = A_2;
                }
                A_0.CommandType = A_3;
                if (A_5 != null)
                {
                    for (int i = 0; i < A_5.Length; i++)
                    {
                        if ((A_5[i].Direction == ParameterDirection.InputOutput || A_5[i].Direction == ParameterDirection.Input) && (A_5[i].Value == null))
                        {
                            A_5[i].Value = DBNull.Value;
                        }
                        A_0.Parameters.Add(A_5[i]);
                    }
                }
            }
            catch
            {
                throw;
            }
        }
        public static SqlParameter[] GetProduceParameter(string cnString, string proName)
        {
            SqlConnection sqlConnection = new SqlConnection(cnString);
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = proName;
            SqlCommandBuilder.DeriveParameters(sqlCommand);
            SqlParameter[] array = new SqlParameter[sqlCommand.Parameters.Count];
            for (int i = 0; i < sqlCommand.Parameters.Count; i++)
            {
                array[i] = new SqlParameter();
                array[i].SqlDbType = sqlCommand.Parameters[i].SqlDbType;
                array[i].ParameterName = sqlCommand.Parameters[i].ParameterName;
                array[i].Size = sqlCommand.Parameters[i].Size;
            }
            return array;
        }

        #region 执行查询语句或存储过程，返回第一行第一列数据或存储过程返回值,此方法只是用于登录


        /// <summary>
        /// 执行存储过程，返回存储过程返回值
        /// </summary>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="ps">参数列表</param>
        /// <returns>存储过程返回值</returns>
        public object ExecuteScalarSP(string cmdText, params SqlParameter[] ps)
        {
            return ExecuteScalarLogin(cmdText, CommandType.StoredProcedure, ps);
        }

        /// <summary>
        /// 执行查询语句或存储过程，返回第一行第一列数据或存储过程返回值
        /// 此方法只是用于登录
        /// </summary>
        /// <param name="cmdText">Sql语句或存储过程名称</param>
        /// <param name="ps">参数列表</param>
        /// <returns>第一行第一列数据或存储过程返回值</returns>
        public object ExecuteScalarLogin(string cmdText, CommandType type, params SqlParameter[] ps)
        {
            if (type == CommandType.Text)
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(SysConnStr))
                    {
                        using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                        {
                            if (ps != null)
                            {
                                cmd.Parameters.AddRange(ps);
                            }
                            conn.Open();
                            return cmd.ExecuteScalar();
                        }
                    }
                }
                catch (Exception ex)
                {
                    return ex;
                }
            }
            else if (type == CommandType.StoredProcedure)
            {
                try
                {
                    for (int i = 0; i < ps.Length; i++)
                    {
                        if (ps[i].Value == null)
                        {
                            ps[i].Direction = ParameterDirection.Output;
                        }
                    }
                    using (SqlConnection conn = new SqlConnection(SysConnStr))
                    {
                        using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                        {
                            cmd.CommandType = type;
                            if (ps != null)
                            {
                                cmd.Parameters.AddRange(ps);
                            }
                            conn.Open();
                            cmd.ExecuteScalar();
                        }
                    }
                    int returnValue = 99;
                    for (int i = 0; i < ps.Length; i++)
                    {
                        if (ps[i].Direction == ParameterDirection.Output)
                        {
                            returnValue = Convert.ToInt32(ps[i].Value);
                        }
                    }
                    return returnValue;
                }
                catch (Exception ex)
                {
                    return ex;
                }
            }
            else
            {
                return -1;
            }
        }


        #endregion



        #region 使用存储过程实现分页
        public DataSet ExecuteDataset(bool InTransaction, CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            DataSet set;
            try
            {
                using (SqlConnection connection = new SqlConnection(SysConnStr))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand();
                    SqlTransaction transaction = null;
                    if (InTransaction)
                    {
                        transaction = connection.BeginTransaction();
                    }
                    this.method_2(command, connection, transaction, commandType, commandText, (SqlParameter[])commandParameters);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    this.method_3(transaction, commandType, command, (SqlParameter[])commandParameters);
                    command.Parameters.Clear();
                    return dataSet;
                }
            }
            catch (Exception ex)
            {
                AnimalError.DealErr(ex);
                set = null;
            }
            finally
            {
            }
            return set;
        }
        private void method_3(SqlTransaction sqlTransaction, CommandType commandType, SqlCommand sqlCommand, SqlParameter[] sqlParameter)
        {
            if (sqlTransaction != null)
            {
                sqlTransaction.Commit();
            }
            if (((commandType == CommandType.StoredProcedure) && (sqlParameter != null)) && (sqlParameter.Length == sqlCommand.Parameters.Count))
            {
                sqlCommand.Parameters.CopyTo(sqlParameter, 0);
            }
        }
        private void method_2(SqlCommand sqlCommand, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CommandType commandType, string commandText, SqlParameter[] sqlParameter)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = commandText;
            if (sqlTransaction != null)
            {
                sqlCommand.Transaction = sqlTransaction;
            }
            sqlCommand.CommandType = commandType;
            if (sqlParameter != null)
            {
                this.method(sqlCommand, sqlParameter);
            }
        }
        private void method(SqlCommand sqlCommand, SqlParameter[] sqlParameter)
        {
            foreach (SqlParameter parameter in sqlParameter)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput) && (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                sqlCommand.Parameters.Add(parameter);
            }
        }

        /// <summary>
        /// 内存回收
        /// </summary>
        public void Dispose()
        {

        }
        #endregion



        public static SqlTransaction CreatTransaction()
        {
            return SQLHelper.CreatTransaction(SQLHelper.Connection);
        }
        public static SqlTransaction CreatTransaction(string strSysConnStr)
        {
            SqlTransaction result;
            try
            {
                SqlConnection sqlConnection = new SqlConnection(strSysConnStr);
                sqlConnection.Open();
                result = sqlConnection.BeginTransaction();
            }
            catch
            {
                throw;
            }
            return result;
        }
        private static void a(SqlConnection A_0, SqlTransaction A_1)
        {
            if (A_0 != null && A_0.State != ConnectionState.Closed && A_1 == null)
            {
                A_0.Close();
            }
        }
    }
}
